import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.*;

public class AlertRepository {
	
	//get alerts for a specific IP, sorted by time and milli_secs 
	public List<Alert> getAlertsByIP(String ip) {
		
		Alert alert = null;
		List<Alert> alert_list = new ArrayList<Alert>();
		Connection connection;
			try {
				connection = DBConnection.getConnection();
				String alertColumns = "event_id, time, milli_secs, alert_id, type, classification, source_ip, destination_ip"; 
				String query = "SELECT " + alertColumns + " FROM alert WHERE destination_ip = ? ORDER BY time ASC, milli_secs ASC;"; 
				PreparedStatement alert_stmt = connection.prepareStatement(query);
				alert_stmt.setString(1, ip);
				ResultSet results = alert_stmt.executeQuery();
				while (results.next()) {
						int ev_id = (results.getInt(1)); // event id
						long time = results.getTime(2).getTime(); // time
						int mil = results.getInt(3); //  milli secs
						int al_id = results.getInt(4); // alert id
						String type = results.getString(5); // type
						String classif = results.getString(6); // classification
						String s_ip = results.getString(7); // source ip
						String d_ip = results.getString(8); // destination ip
						
						alert = new Alert(ev_id, time, mil, al_id, type, classif, s_ip, d_ip);
						alert_list.add(alert);
				}
				
				connection.close();
				
			} catch (SQLException e1) {
		
				e1.printStackTrace();
			}
			
			
			return alert_list;
		}
	 
	public List<Alert> getAllAlerts() {
		
		Alert alert = null;
		List<Alert> alert_list = new ArrayList<Alert>();
		Connection connection;
			try {
				connection = DBConnection.getConnection(); 
				String query = "SELECT * FROM alert ORDER BY destination_ip ASC, time ASC, milli_secs ASC"; 
				Statement alert_stmt = connection.createStatement();
				ResultSet results = alert_stmt.executeQuery(query);
				while (results.next()) {
						int ev_id = (results.getInt(1)); // event id
						long time = results.getTime(2).getTime(); // time
						int mil = results.getInt(3); //  milli secs
						int al_id = results.getInt(4); // alert id
						String type = results.getString(5); // type
						String classif = results.getString(6); // classification
						String s_ip = results.getString(7); // source ip
						String d_ip = results.getString(8); // destination ip
						
						alert = new Alert(ev_id, time, mil, al_id, type, classif, s_ip, d_ip);
						alert_list.add(alert);
				}
				
				connection.close();
				
			} catch (SQLException e1) {
		
				e1.printStackTrace();
			}
			
			
			return alert_list;
		}
	
	
	
	//get all unique destination_ip 
	//Host List
	public List<Host> getHostList() {
		
		Host host;
		List<Host> host_list = new ArrayList<Host>();
		Connection connection;
			try {
				connection = DBConnection.getConnection();
				String alertColumns = "destination_ip"; 
				String query = "SELECT DISTINCT " + alertColumns + " FROM alert;"; 
				Statement alert_stmt = connection.createStatement();
				ResultSet results = alert_stmt.executeQuery(query);
				while (results.next()) {
						String d_ip = results.getString(1); // destination ip
						
						host = new Host(d_ip);
						host_list.add(host);
				}
				
				connection.close();
				
			} catch (SQLException e1) {
		
				e1.printStackTrace();
			}
			
			
			return host_list;
		
		
	}
	
	//TESTING RESPOSITORY, get an alert by event_id
	public Alert getAlertByEventID(int event_id) {
		
		Alert alert = null;
		Connection connection;
			try {
				connection = DBConnection.getConnection();
				String alertColumns = "event_id, time, milli_secs, alert_id, type, classification, source_ip, destination_ip"; 
				String query = "SELECT " + alertColumns + " FROM alert WHERE event_id = ?;"; 
				PreparedStatement alert_stmt = connection.prepareStatement(query);
				alert_stmt.setInt(1, event_id);
				ResultSet results = alert_stmt.executeQuery();
				while (results.next()) {
						int ev_id = (results.getInt(1)); // event id
						long time = results.getTime(2).getTime(); // time
						int mil = results.getInt(3); //  milli secs
						int al_id = results.getInt(4); // alert id
						String type = results.getString(5); // type
						String classif = results.getString(6); // classification
						String s_ip = results.getString(7); // source ip
						String d_ip = results.getString(8); // destination ip
						
						alert = new Alert(ev_id, time, mil, al_id, type, classif, s_ip, d_ip);							
				}
				
				connection.close();
				
			} catch (SQLException e1) {
	
				e1.printStackTrace();
			}
			
			return alert;
				
	}

}
